package Program.SqlTool;

import Program.Information.*;
import Program.Tool.DruidJDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class UserDAO {
    //数据库连接对象
    private static Connection conn;
    //sql执行对象
    private static PreparedStatement pstmt;
    //结果集对象
    private static ResultSet rs;


    //查询相关的方法

    //输入的检查账号是否正确
    public static boolean accountCheck(String username, String userPassword) {
        //sql语句
        String sql = "select * from user where userName = ? and userPassword = ?";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql执行对象
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, userPassword);
            //执行sql
            rs = pstmt.executeQuery();
            if (rs.next()) {
                //登录成功
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        //登录失败
        return false;
    }

    //根据用户名获取当前用户账号所有信息
    public static User selectUser(String userName) {
        //sql语句
        String sql = "select * from user where userName = ?";
        //创建用户对象记录数据
        User user = null;
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userName);
            //执行sql
            rs = pstmt.executeQuery();
            if (rs.next()) {
                // 获得id值
                int userID = rs.getInt("userID");
                // 获得密码
                String userPassword = rs.getString("userPassword");
                //获取名字
                String name = rs.getString("name");
                // 获得性别
                String userSex = rs.getString("userSex");
                //获取用户类型
                String usertype = rs.getString("usertype");
                //获取手机
                String phone = rs.getString("phone");
                //获取预存款
                double adDeposit = rs.getDouble("adDeposit");
                // 获得地址
                String place = rs.getString("place");
                //获取用户状态
                String state = rs.getString("state");

                user = new User(userID, userName, userPassword, name, userSex, usertype, phone, adDeposit, place, state);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        //返回用户
        return user;
    }

    //根据用户id获取当前用户账号所有信息
    public static User selectUser(int userID) {
        //sql语句
        String sql = "select * from user where userID = ?";
        //创建用户对象记录数据
        User user = null;
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            //执行sql
            rs = pstmt.executeQuery();
            if (rs.next()) {
                // 获得用户名
                String userName = rs.getString("userName");
                // 获得密码
                String userPassword = rs.getString("userPassword");
                //获取名字
                String name = rs.getString("name");
                // 获得性别
                String userSex = rs.getString("userSex");
                //获取用户类型
                String usertype = rs.getString("usertype");
                //获取手机
                String phone = rs.getString("phone");
                //获取预存款
                double adDeposit = rs.getDouble("adDeposit");
                // 获得地址
                String place = rs.getString("place");
                //获取用户状态
                String state = rs.getString("state");

                user = new User(userID, userName, userPassword, name, userSex, usertype, phone, adDeposit, place, state);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        //返回用户
        return user;
    }


    //查看用户该月的费用
    public static Fee lookFee(int userID, String day) {
        Fee fee = new Fee();
        //创建视图sql语句
        String sql1 = "CREATE OR REPLACE VIEW `Fee` AS " +
                "SELECT `usage`.`id` AS '账单编号'," +
                "       `usage`.`userID` AS '用户ID'," +
                "       ROUND(`usage`.`waterUsage` * `usertype_fee`.`waterFee`, 2) AS '水费'," +
                "       ROUND(`usage`.`electricUsage` * `usertype_fee`.`electricFee`, 2) AS '电费'," +
                "       ROUND(`usage`.`gasUsage` * `usertype_fee`.`gasFee`, 2) AS '天然气费'," +
                "       ROUND(`usage`.`parkDay` * `usertype_fee`.`parkFee`, 2) AS '停车费'," +
                "       `usage`.`day` AS '日期'," +
                "       ROUND(ROUND(`usage`.`waterUsage` * `usertype_fee`.`waterFee`, 2)+ROUND(`usage`.`electricUsage` * `usertype_fee`.`electricFee`, 2)+ROUND(`usage`.`gasUsage` * `usertype_fee`.`gasFee`, 2)+ROUND(`usage`.`parkDay` * `usertype_fee`.`parkFee`, 2),2) AS '总费用'," +
                "       `usage`.`state` AS '支付状态'" +
                "FROM `usage`" +
                "JOIN `user` ON `usage`.`userID` = `user`.`userID`" +
                "JOIN `usertype_fee` ON `usertype_fee`.`usertype` = `user`.`usertype`" +
                "WHERE `user`.`userID` = ? and `usage`.`day` = ?";
        //查询视图sql语句
        String sql2 = "select * from `Fee`";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //创建视图
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql1);
            pstmt.setInt(1, userID);
            pstmt.setString(2, day);
            //执行创建视图sql语句
            pstmt.executeUpdate();
            //sql语句执行对象的设置
            //查询视图
            pstmt = conn.prepareStatement(sql2);
            //执行查询视图sql语句
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                //账单编号
                int id = rs.getInt("账单编号");
                //水费
                double waterFee = rs.getDouble("水费");
                //电费
                double electricFee = rs.getDouble("电费");
                //天然气费
                double gasFee = rs.getDouble("天然气费");
                //停车费
                int parkFee = rs.getInt("停车费");
                //总费用
                double sumFee = rs.getDouble("总费用");
                //获取支付状态
                String state = rs.getString("支付状态");
                fee = new Fee(id, userID, waterFee, electricFee, gasFee, parkFee, day, sumFee, state);
            } else {
                fee = new Fee(0, userID, 0.0, 0.0, 0.0, 0, day, 0.0, "-1");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return fee;
    }

    //获取该用户所有的账单
    public static ArrayList<Fee> lookFees(int userID) {
        ArrayList<Fee> list = new ArrayList<>();
        //创建视图sql语句
        String sql1 = "CREATE OR REPLACE VIEW `Fee` AS " +
                "SELECT `usage`.`id` AS '账单编号'," +
                "       `usage`.`userID` AS '用户ID'," +
                "       ROUND(`usage`.`waterUsage` * `usertype_fee`.`waterFee`, 2) AS '水费'," +
                "       ROUND(`usage`.`electricUsage` * `usertype_fee`.`electricFee`, 2) AS '电费'," +
                "       ROUND(`usage`.`gasUsage` * `usertype_fee`.`gasFee`, 2) AS '天然气费'," +
                "       ROUND(`usage`.`parkDay` * `usertype_fee`.`parkFee`, 2) AS '停车费'," +
                "       `usage`.`day` AS '日期'," +
                "       ROUND(ROUND(`usage`.`waterUsage` * `usertype_fee`.`waterFee`, 2)+ROUND(`usage`.`electricUsage` * `usertype_fee`.`electricFee`, 2)+ROUND(`usage`.`gasUsage` * `usertype_fee`.`gasFee`, 2)+ROUND(`usage`.`parkDay` * `usertype_fee`.`parkFee`, 2),2) AS '总费用'," +
                "       `usage`.`state` AS '支付状态'" +
                "FROM `usage`" +
                "JOIN `user` ON `usage`.`userID` = `user`.`userID`" +
                "JOIN `usertype_fee` ON `usertype_fee`.`usertype` = `user`.`usertype`" +
                "WHERE `user`.`userID` = ?";
        //查询视图sql语句
        String sql2 = "select * from `Fee`";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //创建视图
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql1);
            pstmt.setInt(1, userID);
            //执行创建视图sql语句
            pstmt.executeUpdate();
            //sql语句执行对象的设置
            //查询视图
            pstmt = conn.prepareStatement(sql2);
            //执行查询视图sql语句
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                //账单编号
                int id = rs.getInt("账单编号");
                //水费
                double waterFee = rs.getDouble("水费");
                //电费
                double electricFee = rs.getDouble("电费");
                //天然气费
                double gasFee = rs.getDouble("天然气费");
                //停车费
                int parkFee = rs.getInt("停车费");
                //日期
                String day = rs.getString("日期");
                //总费用
                double sumFee = rs.getDouble("总费用");
                //获取支付状态
                String state = rs.getString("支付状态");
                if (state.equals("0")) {
                    continue;
                }
                Fee fee = new Fee(id, userID, waterFee, electricFee, gasFee, parkFee, day, sumFee, state);
                list.add(fee);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return list;
    }

    //获取存在账单的日期
    public static ArrayList<String> getUsageDay(int userID) {
        ArrayList list = new ArrayList();
        //创建sql语句
        String sql = "SELECT day FROM `usage` WHERE userID = ?";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            rs = pstmt.executeQuery();
            String day = "";
            while (rs.next()) {
                day = rs.getString("day");
                list.add(day);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return list;
    }

    //获取当前账号类型的收费标准
    public static UsertypeFee getUsertypeFee(String usertype) {
        //用于记录数据
        UsertypeFee utf = null;
        //sql语句
        String sql = "select * from `usertype_fee` where usertype = ?";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, usertype);
            //执行sql
            rs = pstmt.executeQuery();
            if (rs.next()) {
                double waterFee = rs.getDouble("waterFee");
                double electricFee = rs.getDouble("electricFee");
                int parkFee = rs.getInt("parkFee");
                double gasFee = rs.getDouble("gasFee");
                utf = new UsertypeFee(usertype, waterFee, electricFee, parkFee, gasFee);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return utf;
    }

    //获取当前账号的所有报修单
    public static ArrayList<Repair> getUserRepairs(int userID) {
        //sql语句
        String sql = "select * from `repair` WHERE userID = ?";
        //创建报修对象记录数据
        Repair repair = null;
        //创建集合记录所有的报修单
        ArrayList<Repair> list = new ArrayList<>();
        //该用户的报修单显示编号
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            //执行sql
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //保修单编号
                int id = rs.getInt("id");
                //物品
                String article = rs.getString("article");
                //住址
                String place = rs.getString("place");
                //报修时间
                String startTime = rs.getString("startTime");
                //维修时间
                String endTime = rs.getString("endTime");
                //报修详情
                String details = rs.getString("details");
                //处理状态
                String state = rs.getString("state");

                repair = new Repair(id, article, place, startTime, endTime, details, state);
                list.add(repair);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return list;
    }

    //获取当前账号的所有投诉单
    public static ArrayList<Complaint> getUserComplaints(int userID) {
        //sql语句
        String sql = "select * from `complaint` WHERE userID = ?";
        //创建对象记录数据
        Complaint complaint = null;
        //创建集合记录所有的投诉单
        ArrayList<Complaint> list = new ArrayList<>();
        //该用户的投诉单显示编号
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            //执行sql
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //投诉单编号
                int id = rs.getInt("id");
                //投诉时间
                String startTime = rs.getString("startTime");
                //处理时间
                String endTime = rs.getString("endTime");
                //投诉详情
                String details = rs.getString("details");
                //处理状态
                String state = rs.getString("state");

                complaint = new Complaint(id, startTime, endTime, details, state);
                list.add(complaint);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return list;
    }


    //增删改相关方法

    //添加新的用户账号
    public static boolean addNewUser(String userName, String userPassword, String name, String userSex,
                                     String usertype, String phone, String place) {
        //sql语句
        String sql = "insert into user(userID,userName,userPassword,name,userSex,usertype,phone,adDeposit,place,state)" +
                "VALUES(null,?,?,?,?,?,?,0.0,?,'1')";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userName);
            pstmt.setString(2, userPassword);
            pstmt.setString(3, name);
            pstmt.setString(4, userSex);
            pstmt.setString(5, usertype);
            pstmt.setString(6, phone);
            pstmt.setString(7, place);
            //执行sql
            int i = pstmt.executeUpdate();
            if (i == 1) {
                //创建成功
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        //创建失败
        return false;
    }

    //删除用户账号
    public static boolean deleteUser(String userName) {

        //删除sql语句
        String sql = "delete from user where userName = ?";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userName);
            //执行sql
            int i = pstmt.executeUpdate();
            if (i == 1) {
                UserDAO.updateID("user");
                //删除成功
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        //创建失败
        return false;
    }

    //删除用户账号
    public static boolean deleteUser(int userID) {
        //删除sql语句
        String sql = "delete from `user` WHERE userID = ?";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            //执行sql
            int i = pstmt.executeUpdate();
            if (i == 1) {
                //删除成功
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        //创建失败
        return false;
    }

    //更改用户账号密码
    public static boolean updatePassword(String userName, String userPassword, String phone) {
        //sql语句
        String sql = "update user set userPassword = ? where userName = ? and phone = ?";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userPassword);
            pstmt.setString(2, userName);
            pstmt.setString(3, phone);
            //执行sql
            int i = pstmt.executeUpdate();
            if (i == 1) {
                //更改成功
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        //更改失败
        return false;
    }


    //删除账单
    public static boolean delFee(int id) {
        //sql语句
        String sql = "DELETE FROM `usage` WHERE `usage`.id = ?";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }


    //更新支付状态
    public static void updatePayState(int feeID) {
        //sql语句
        String sql = "UPDATE `usage` SET `usage`.state = ? WHERE `usage`.id = ?";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "1");
            pstmt.setInt(2, feeID);
            pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
    }

    //更新用户余额
    public static void updateAdDeposit(String userName, double sum) {
        //sql语句
        String sql = "UPDATE `user` SET `user`.adDeposit = ? WHERE `user`.userName = ?";

        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1, sum);
            pstmt.setString(2, userName);
            pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
    }

    //添加报修单
    public static boolean addRepair(int userID, String article, String place, String startTime, String details) {
        //sql语句
        String sql = "INSERT INTO `repair` (id,userID,article,place,startTime,endTime,details,state)" +
                "VALUES (NULL,?,?,?,?,'---',?,'0')";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            pstmt.setString(2, article);
            pstmt.setString(3, place);
            pstmt.setString(4, startTime);
            pstmt.setString(5, details);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //删除报修单
    public static boolean delRepair(int id) {
        //sql语句
        String sql = "DELETE FROM `repair` WHERE `repair`.id = ?";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //添加投诉单
    public static boolean addComplaint(int userID, String startTime, String details) {
        //sql语句
        String sql = "INSERT INTO `complaint` (id,userID,startTime,endTime,details,state)" +
                "VALUES (NULL,?,?,'---',?,'0')";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            pstmt.setString(2, startTime);
            pstmt.setString(3, details);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //删除投诉单
    public static boolean delComplaint(int id) {
        //sql语句
        String sql = "DELETE FROM `complaint` WHERE `complaint`.id = ?";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }


    //删除投诉单
    public static boolean delRecharge(int id) {
        //sql语句
        String sql = "DELETE FROM `recharge` WHERE `recharge`.id = ?";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //更新id
    public static void updateID(String tableName) {
        //更新自增id的sql语句
        String sql1 = "SET @auto_id = 0";
        String sql2 = "UPDATE `" + tableName + "` SET id = (@auto_id := @auto_id + 1)";
        String sql3 = "ALTER TABLE `" + tableName + "` AUTO_INCREMENT = 1";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql1);
            pstmt.executeUpdate();
            pstmt = conn.prepareStatement(sql2);
            pstmt.executeUpdate();
            pstmt = conn.prepareStatement(sql3);
            pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
    }

    //添加充值单
    public static boolean addRecharge(int userID, String date, double amount) {
        //sql语句
        String sql = "INSERT INTO `recharge` (id,userID,startTime,amount,state)" +
                "VALUES (NULL,?,?,?,'0')";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,userID);
            pstmt.setString(2,date);
            pstmt.setDouble(3,amount);
            int i = pstmt.executeUpdate();
            if (i==1){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //更新userID
    public static void updateUserID(String tableName) {
        //更新自增id的sql语句
        String sql1 = "SET @auto_id = 0";
        String sql2 = "UPDATE `" + tableName + "` SET userID = (@auto_id := @auto_id + 1)";
        String sql3 = "ALTER TABLE `" + tableName + "` AUTO_INCREMENT = 1";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql1);
            pstmt.executeUpdate();
            pstmt = conn.prepareStatement(sql2);
            pstmt.executeUpdate();
            pstmt = conn.prepareStatement(sql3);
            pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
    }
}
